﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using iAnywhere.Data.SQLAnywhere;
using Oracle.DataAccess.Client;
using System.Configuration;
using System.Globalization;


namespace SybaseTest
{
    class Program
    {
        static string unifyshowStr;
        static Dictionary<string, int> companyDic = new Dictionary<string, int>();
        static DateTime timestamp;
        static bool hasTimeStamp = false;
        static Dictionary<string, int> bsDic = new Dictionary<string, int>();


        internal static void Main(string[] args)
        {

            if (args.Length < 1)
            {
                timestamp = Convert.ToDateTime("1970-1-1 0:0:0");
                //timestamp = Convert.ToDateTime("2014-1-1 0:0:0");
                hasTimeStamp = false;
            }
            else
            {
                timestamp = Convert.ToDateTime(args[0]);
                hasTimeStamp = true;
            }

            SysLog.WriteSysLog("Message write Time:" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
            Console.WriteLine("Begin to sync BPPM to UnifyShow...");

            unifyshowStr = ConfigurationManager.AppSettings["unify"];
            if (string.IsNullOrEmpty(unifyshowStr))
            {
                Console.WriteLine("Missing unifyshowStr, this configuration can not be empty.");
                SysLog.WriteSysLog("Missing unifyshowStr, this configuration can not be empty.");
                Console.Read();
                return;
            }

            using (OracleDataAccess oc = new OracleDataAccess())
            {
                //open oracel
                oc.Open();

                //init company dic
                string sql0 = @"select id,name from sitebranch";
                OracleDataReader reader = null;
                try
                {
                    reader = oc.ExecuteReader(sql0);
                    while (reader.Read())
                    {
                        companyDic.Add(reader["name"].ToString(), Convert.ToInt32(reader["id"]));
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Init site failed. \n" + ex.ToString());
                    SysLog.WriteSysLog("Init site failed." + ex.ToString());
                    throw ex;
                }
                finally
                {
                    reader.Close();
                }

                //init bs
                string sql = @"select b.id as id,b.name as bname,s.name as cname from BUSINESSSYSTEM b inner join sitebranch s on b.siteid = s.id";
                try
                {
                    reader = oc.ExecuteReader(sql);
                    while (reader.Read())
                    {
                        if (!bsDic.ContainsKey(reader["bname"].ToString() + reader["cname"].ToString()))
                            bsDic.Add(reader["bname"].ToString() + reader["cname"].ToString(), Convert.ToInt32(reader["id"]));
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Init businessSystem failed. \n" + ex.ToString());
                    SysLog.WriteSysLog("Init businessSystem failed." + ex.ToString());
                }
                finally
                {
                    reader.Close();
                }


                //loop to read bppm server
                foreach (var bppmConnKey in ConfigurationManager.AppSettings.Keys)
                {
                    if (bppmConnKey.ToString() == "unify") continue; //skip unify

                    string bppmConn = ConfigurationManager.AppSettings[bppmConnKey.ToString()];

                    if (string.IsNullOrEmpty(bppmConn)) //skip empty value
                    {
                        Console.WriteLine("The value of " + bppmConnKey + " is empty, skip it.");
                        continue;
                    }

                    try
                    {
                        //import
                        Import(oc, bppmConn, bppmConnKey.ToString());
                        ImportBs(oc, bppmConn, bppmConnKey.ToString());
                    }
                    catch (Exception ex)
                    {
                        SysLog.WriteSysLog(ex.ToString());
                    }
                }

            }

            Console.WriteLine("Compelete.");
        }

        public static void EnsureCmdAndReader(SACommand cmd, SADataReader reader)
        {
            if (reader != null) reader.Close();
            if (cmd != null) cmd.Dispose();
        }

        //oda is already open
        //key is company name
        //bppmStr is bppm connection string
        static void Import(OracleDataAccess oda, string bppmStr, string key)
        {
            string sql = string.Empty;
            using (SAConnection conn = new SAConnection(bppmStr))
            {
                conn.Open();

                /////开始导入Device
                //查询所有的Deivce，服务器会聚合，其他不聚合
                sql = @"select min(MOINSTID) as Mid,DeviceName,Type,Object,'' as Instname from report.t_device where type='服务器'
                      group by DeviceName,Type,Object
                      union
                      select min(MOINSTID) as Mid,DeviceName,Type,Object,Instname from report.t_device where type != '服务器'
                      group by DeviceName,Type,Object,Instname";

                SACommand cmd = new SACommand(sql, conn);
                SADataReader rd = cmd.ExecuteReader();
                List<DeviceFromBPPM> devices = new List<DeviceFromBPPM>();

                while (rd.Read())
                {
                    devices.Add(new DeviceFromBPPM
                    {
                        Mid = (int)rd["Mid"],
                        Identify = rd["DeviceName"].ToString(),
                        Type = rd["Type"].ToString(),
                        Object = rd["Object"].ToString(),
                        SubObject = rd["Instname"].ToString()
                    });
                }

                EnsureCmdAndReader(cmd, rd);

                //查询oracle中的数据进行同步
                //遍历devices，看oracle中有没有记录，没有的话就添加，否则不做动作
                foreach (var device in devices)
                {
                    try
                    {
                        //OracleParameter[] param0 = new OracleParameter[] { 
                        //        new OracleParameter("identify",device.Identify),
                        //        new OracleParameter("type",device.Type),
                        //        new OracleParameter("object",device.Object),
                        //        new OracleParameter("subobject",null)
                        //    };

                        sql = "select id from Device where";
                        if (string.IsNullOrEmpty(device.Identify))
                            sql += " identify is null";
                        else
                            sql += " identify='" + device.Identify + "'";
                        if (string.IsNullOrEmpty(device.Type))
                            sql += " and type is null";
                        else
                            sql += " and type='" + device.Type + "'";
                        if (string.IsNullOrEmpty(device.Object))
                            sql += " and object is null";
                        else
                            sql += " and object='" + device.Object + "'";
                        if (string.IsNullOrEmpty(device.SubObject))
                            sql += " and subobject is null";
                        else
                            sql += " and subobject='" + device.SubObject + "'";

                        if (oda.ExecuteScalar(sql) == null) //没有对应记录，需要添加
                        {
                            OracleParameter[] param = new OracleParameter[] { 
                                new OracleParameter("id",null),
                                new OracleParameter("bppmid",null),
                                new OracleParameter("siteid",companyDic[key]),
                                new OracleParameter("bsid",null),
                                new OracleParameter("identify",device.Identify),
                                new OracleParameter("type",device.Type),
                                new OracleParameter("object",device.Object),
                                new OracleParameter("subobject",device.SubObject),
                                new OracleParameter("createtime",DateTime.Now),
                            };

                            oda.ExecuteCommand(
                                "insert into device values(:id,:bppmid,:siteid,:bsid,:identify,:type,:object,:subobject,:createtime)",
                                param);
                            Console.WriteLine("One Device Inserted.");
                        }
                    }
                    catch (Exception ex)
                    {
                        SysLog.WriteSysLog(ex.ToString());
                    }

                }


                /////----------------------------------开始导入performance

                //读取oracle库中所有该地市的device，并做成字典
                //key : identify+type+object+subobject
                Dictionary<string, int> deviceInOracle = new Dictionary<string, int>();
                int companyId = companyDic[key];
                sql = @"select id,identify,type,object,subobject from device where siteid=" + companyId;

                var deviceReader = oda.ExecuteReader(sql);
                while (deviceReader.Read())
                {
                    string key3 = deviceReader["identify"].ToString() + deviceReader["type"].ToString() + deviceReader["object"].ToString() + deviceReader["subobject"].ToString();
                    if (deviceInOracle.ContainsKey(key3))
                        continue;
                    deviceInOracle.Add(key3, Convert.ToInt32(deviceReader["id"]));
                }

                deviceReader.Close();


                //读取sybase中的性能数据，并遍历,
                //对于每条性能数据，在oracle中找，如果找到，则不处理 ，如果找不到则插入performace表
                sql = @"select p.IP,d.Type,d.Object,d.Instname,p.Parameter,(p.ParameterDisplay + '-' + d.Instname) as ParameterDisplay,p.value,p.fromTime as timestamp
                      from report.t_performance p
                      inner join report.t_device d on d.MOINSTID = p.DeviceId";

                SACommand cmd2 = new SACommand(sql, conn);
                SADataReader rd2 = cmd2.ExecuteReader();

                while (rd2.Read())
                {
                    try
                    {

                        string sql3 = null;
                        List<OracleParameter> OracleParameters = new List<OracleParameter>();
                        //TODO：服务器不需要判别subobject
                        if (rd2["Type"].ToString() == "服务器")
                        {
                            sql3 = string.Format(@"select 
--d.Id,d.Identify,d.Type,d.Object,d.SubObject,p.ParameterDisplay,p.Timestamp
p.Id
from performance p
inner join device d on p.deviceid = d.Id
where d.Identify=:identify and d.Type=:type and d.Object=:object and p.ParameterDisplay=:parameterdisplay and p.Timestamp=to_date('{0}','mm/dd/yyyy hh24:mi:ss')", (new DateTime(1970, 1, 1).AddSeconds(Convert.ToInt32(rd2["timestamp"])).AddHours(8)).ToString("MM/dd/yyyy HH:mm:ss", DateTimeFormatInfo.InvariantInfo));

                            OracleParameters.Add(new OracleParameter("identify", rd2["IP"]));
                            OracleParameters.Add(new OracleParameter("type", rd2["Type"]));
                            OracleParameters.Add(new OracleParameter("object", rd2["Object"]));
                            OracleParameters.Add(new OracleParameter("parameterdisplay", rd2["ParameterDisplay"]));
                            //OracleParameters.Add(new OracleParameter("timestamp", ));
                        }
                        else
                        {
                            sql3 = string.Format(@"select 
--d.Id,d.Identify,d.Type,d.Object,d.SubObject,p.ParameterDisplay,p.Timestamp
p.Id
from performance p
inner join device d on p.deviceid = d.Id
where d.Identify=:identify and d.Type=:type and d.Object=:object and d.SubObject=:subobject and p.ParameterDisplay=:parameterdisplay and p.Timestamp=to_date('{0}','mm/dd/yyyy hh24:mi:ss')", (new DateTime(1970, 1, 1).AddSeconds(Convert.ToInt32(rd2["timestamp"])).AddHours(8)).ToString("MM/dd/yyyy HH:mm:ss", DateTimeFormatInfo.InvariantInfo));


                            OracleParameters.Add(new OracleParameter("identify", rd2["IP"]));
                            OracleParameters.Add(new OracleParameter("type", rd2["Type"]));
                            OracleParameters.Add(new OracleParameter("object", rd2["Object"]));
                            OracleParameters.Add(new OracleParameter("subobject", rd2["Instname"]));
                            OracleParameters.Add(new OracleParameter("parameterdisplay", rd2["ParameterDisplay"]));
                            //OracleParameters.Add(new OracleParameter("timestamp", rd2["timestamp"]));
                        }

                        if (oda.ExecuteScalar(sql3, OracleParameters.ToArray()) == null) //没有对应记录，需要添加
                        {
                            string key2 = null;
                            //从deviceInOracle字典里检索deivceid
                            //TODO：服务器不需要判别subobject
                            if (rd2["Type"].ToString() == "服务器")
                            {
                                key2 = rd2["IP"].ToString() + rd2["Type"].ToString() + rd2["Object"].ToString();
                            }
                            else
                            {
                                key2 = rd2["IP"].ToString() + rd2["Type"].ToString() + rd2["Object"].ToString() + rd2["Instname"].ToString();
                            }
                            if (!deviceInOracle.ContainsKey(key2))
                            {
                                SysLog.WriteSysLog(key2 + " can not match device.");
                            }
                            PerformanceValue value = ConvertValue(rd2["Parameter"], rd2["value"]);

                            OracleParameter[] param = new OracleParameter[] { 
                                new OracleParameter("id",null),
                                new OracleParameter("deviceid",deviceInOracle[key2]),
                                new OracleParameter("parameter",rd2["Parameter"]),
                                new OracleParameter("parameterdisplay",rd2["ParameterDisplay"]),
                                new OracleParameter("value",value.Value),
                                new OracleParameter("timestamp",new DateTime(1970,1,1).AddSeconds(Convert.ToInt32(rd2["timestamp"])).AddHours(8)),
                                new OracleParameter("createtime",DateTime.Now),
                                new OracleParameter("unit",value.Unit),
                            };

                            oda.ExecuteCommand(
                                "insert into performance values(:id,:deviceid,:parameter,:parameterdisplay,:value,:timestamp,:createtime,:unit)",
                                param);


                            Console.WriteLine("One Performance Inserted.");
                        }
                    }
                    catch (Exception ex)
                    {
                        SysLog.WriteSysLog(ex.ToString());
                    }
                }

                EnsureCmdAndReader(cmd2, rd2);

            }

            GC.Collect();
        }

        //todo : convert performance value to value with unit
        static PerformanceValue ConvertValue(object parameter, object value)
        {
            if (parameter.ToString() == "MEMFREEMEM")
            {
                return new PerformanceValue
                {
                    Value = value.ToString(),
                    Unit = "KB"
                };
            }
            else if (parameter.ToString() == "MEMORYAVAILABLE" || parameter.ToString() == "MEMMEMAVAILABLEBYTES")
            {
                return new PerformanceValue
                {
                    Value = value.ToString(),
                    Unit = "MB"
                };
            }
            else
            {
                return new PerformanceValue
                {
                    Value = value.ToString(),
                    Unit = "%"
                };
            }
        }

        static void ImportBs(OracleDataAccess oda, string bppmStr, string key)
        {

            //read 业务系统可用性
            string sql1 = string.Empty;
            using (SAConnection conn = new SAConnection(bppmStr))
            {
                conn.Open();

                /////开始导入业务系统可用性
                //查询所有的report.t_tmartavailability
                if (hasTimeStamp)
                    sql1 = @"select project_name,value,fromtime,totime,createtime from report.t_tmartavailability where createtime > '" + timestamp + "'";
                else
                    sql1 = @"select project_name,value,fromtime,totime,createtime from report.t_tmartavailability";

                SACommand cmd = new SACommand(sql1, conn);
                SADataReader rd = null;
                List<BSAInSybase> bsAv = new List<BSAInSybase>();
                try
                {
                    rd = cmd.ExecuteReader();


                    while (rd.Read())
                    {
                        bsAv.Add(new BSAInSybase
                        {
                            BSName = rd["project_name"].ToString(),
                            Avg = rd["value"].ToString(),
                            FromTime = Convert.ToInt32(rd["fromtime"]),
                            ToTime = Convert.ToInt32(rd["totime"]),
                            CreateTime = Convert.ToDateTime(rd["createtime"])
                        });
                    }

                }
                catch
                { }
                finally
                {
                    EnsureCmdAndReader(cmd, rd);
                }

                foreach (var bs in bsAv)
                {
                    try
                    {
                        int bid = bsDic[bs.BSName + key];
                        //todo change the datetime format in oracle
                        string selectsql = string.Format("select id from businessavailable where createtime = to_date('{0}','mm/dd/yyyy hh24:mi:ss') and businesssystemid = {1}", bs.CreateTime.ToString("MM/dd/yyyy HH:mm:ss", DateTimeFormatInfo.InvariantInfo), bid);
                        if (oda.ExecuteScalar(selectsql) == null)
                        {
                            string insertsql = string.Empty;
                            DateTime d = new DateTime(1970, 1, 1).AddSeconds(bs.FromTime).AddHours(8);
                            insertsql = "insert into businessavailable values(:id,:bid,:avg,:timestamp,:createtime)";

                            OracleParameter[] param = new OracleParameter[] { 
                                new OracleParameter("id",null),
                                new OracleParameter("bid",bid),
                                new OracleParameter("avg",Convert.ToDouble(bs.Avg)),
                                new OracleParameter("createtime",bs.CreateTime),
                                new OracleParameter("timestamp",d)
                            };


                            oda.ExecuteCommand(insertsql, param);

                            Console.WriteLine("One BusinessAva Inserted.");
                        }
                    }
                    catch
                    {
                        continue;
                    }

                }

            }

        }
    }
}
